package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Building;
import com.xqgl.dao.BuildingDao;
import com.xqgl.util.ConnertionUtil;

public class BuildingDaoImp implements BuildingDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Building> getAllUserInfos() {
		ArrayList<Building> buildings = new ArrayList<>();
		String sql = "select * from building ";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rst=ps.executeQuery(sql);
			rst.last();
			rst.beforeFirst();
			while(rst.next()) {
				Building building = new Building();
				building.setId(rst.getInt("id"));
				building.setVillagename(rst.getString("villagename"));
				building.setBuildingnum(rst.getString("buildingnum"));
				building.setBuildingname(rst.getString("buildingname"));
				building.setBuildinghs(rst.getInt("buildinghs"));
				building.setBuildingdesc(rst.getString("buildingdesc"));
				building.setBuildingctime(rst.getString("buildingctime"));
				buildings.add(building);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return buildings;
	}
	
	@Override
	public int add(Building building) throws SQLException {
		String sql = "insert into building(villagename,buildingnum,buildingname,buildinghs,buildingdesc,buildingctime)"
				+ "values(?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, building.getVillagename());
		ps.setString(2, building.getBuildingnum());
		ps.setString(3, building.getBuildingname());
		ps.setInt(4, building.getBuildinghs());
		ps.setString(5, building.getBuildingdesc());
		ps.setString(6, building.getBuildingctime());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}

	@Override
	public int update(Building building) throws SQLException {
		String sql = "update building set villagename = '"+building.getVillagename()+"', buildingnum = '"+building.getBuildingnum()+"', buildingname = '"+building.getBuildingname()+"', buildinghs = '"+building.getBuildinghs()+"', buildingdesc = '"+building.getBuildingdesc()+"', buildingctime = '"+building.getBuildingctime()+"' where id = "+building.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from building where id = "+id+"";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Building findById(int id) throws SQLException {
		Building building = new Building();
		String sql = "select * from building where id = ?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet set = ps.executeQuery();
		while(set.next()){
			building.setId(set.getInt("id"));
			building.setVillagename(set.getString("villagename"));
			building.setBuildingnum(set.getString("buildingnum"));
			building.setBuildingname(set.getString("buildingname"));
			building.setBuildinghs(set.getInt("buildinghs"));
			building.setBuildingdesc(set.getString("buildingdesc"));
		}
		ps.close();
		return building;
	}
	
}
